package com.libraryWeb.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.libraryWeb.entity.Book;
import com.libraryWeb.util.DateUtil;
import com.libraryWeb.util.JDBCUtil;


/**
 * @author enbo
 * @date 2021-6-5 11:28:16
 */
public class BookDao {

	public Book qryById(int id) {
		// 访问数据库 MySQL
		Connection conn = null;
		PreparedStatement stat = null;
		ResultSet rs = null;
		Book p = null;
		try {
			// 1注册驱动 (只做一次)
			// 2建立连接(Connection)
			conn = JDBCUtil.getConnection();
			// 3创建执行SQL的语句(Statement)
			String sql = "SELECT * FROM books WHERE id=?";
			stat = conn.prepareStatement(sql);
			stat.setInt(1, id);
			// 4执行语句
			rs = stat.executeQuery();
			// 5处理执行结果(ResultSet)
			while (rs.next()) {
				p = new Book();
				p.setId(rs.getInt("id"));
				p.setName(rs.getString("name"));
				p.setPrice(rs.getDouble("price"));
				p.setCounts(rs.getInt("counts"));
				p.setAuthor(rs.getString("author"));
				p.setCrtTime(rs.getDate("crtTime"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 6释放资源
			JDBCUtil.close(conn, stat, rs);
		}

		return p;
	}

	public List<Book> qryAll() {
		// 访问数据库 MySQL
		Connection conn = null;
		Statement stat = null;
		ResultSet rs = null;
		List<Book> list = new ArrayList<Book>();
		try {
			// 1注册驱动 (只做一次)
			// 2建立连接(Connection)
			conn = JDBCUtil.getConnection();
			// 3创建执行SQL的语句(Statement)
			stat = conn.createStatement();
			// 4执行语句
			String sql = "SELECT * FROM books";
			rs = stat.executeQuery(sql);
			// 5处理执行结果(ResultSet)
			while (rs.next()) {
				Book p = new Book();
				int id = rs.getInt("id");
				p.setId(id);
				String name = rs.getString("name");
				p.setName(name);
				p.setPrice(rs.getDouble("price"));
				p.setCounts(rs.getInt("counts"));
				p.setAuthor(rs.getString("author"));
				p.setCrtTime(rs.getDate("crtTime"));

				list.add(p);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 6释放资源
			JDBCUtil.close(conn, stat, rs);
		}

		return list;
	}

	public int insert(Book p) {
		int flag = -1;
		// 访问数据库 MySQL
		Connection conn = null;
		// Statement stat = null;
		PreparedStatement stat = null;// SQL 注入 性能更好
		try {
			// 2建立连接(Connection)
			conn = JDBCUtil.getConnection();
			// 3创建执行SQL的语句(Statement)
			// stat = conn.createStatement();
			String sql = "INSERT INTO books VALUES(NULL,?,?,?,?,?);";
			stat = conn.prepareStatement(sql);
			stat.setString(1, p.getName());
			stat.setDouble(2, p.getPrice());
			stat.setInt(3, p.getCounts());
			stat.setString(4, p.getAuthor());
			stat.setDate(5, DateUtil.utilToSql(p.getCrtTime()));// util.Date --> sql.Date
			// 4执行语句
			flag = stat.executeUpdate();// 不能传sql 切记！！
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 6释放资源
			JDBCUtil.close(conn, stat, null);
		}
		return flag;
	}

	public int update(Book p) {
		int flag = -1;
		// 访问数据库 MySQL
		Connection conn = null;
		// Statement stat = null;
		PreparedStatement stat = null;// SQL 注入 性能更好
		try {
			// 2建立连接(Connection)
			conn = JDBCUtil.getConnection();
			// 3创建执行SQL的语句(Statement)
			// stat = conn.createStatement();
			String sql = "UPDATE books SET name=?,price=?,counts=?,author=?,crtTime=? WHERE id=?";
			stat = conn.prepareStatement(sql);
			stat.setString(1, p.getName());
			stat.setDouble(2, p.getPrice());
			stat.setInt(3, p.getCounts());
			stat.setString(4, p.getAuthor());
			stat.setDate(5, DateUtil.utilToSql(p.getCrtTime()));// util.Date --> sql.Date
			stat.setInt(6, p.getId());
			// 4执行语句
			flag = stat.executeUpdate();// 不能传sql 切记！！
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 6释放资源
			JDBCUtil.close(conn, stat, null);
		}
		return flag;
	}

	public int delete(int id) {
		int flag = -1;
		// 访问数据库 MySQL
		Connection conn = null;
		// Statement stat = null;
		PreparedStatement stat = null;// SQL 注入 性能更好
		try {
			// 2建立连接(Connection)
			conn = JDBCUtil.getConnection();
			// 3创建执行SQL的语句(Statement)
			String sql = "DELETE FROM books WHERE id = ?";
			stat = conn.prepareStatement(sql);
			stat.setInt(1, id);
			// 4执行语句
			flag = stat.executeUpdate();// 不能传sql 切记！！
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 6释放资源
			JDBCUtil.close(conn, stat, null);
		}
		return flag;
	}

}
